
import pymysql
import xlrd
#编程实现：循环输入10个人的信息（姓名，年龄，性别，身高），并存储到数据库中
def  function1():
    #连接 MySQL 数据库
    db = pymysql.connect(
        host='localhost',  # 主机名
        port=3306,         # 端口号
        user='root',       # 用户名
        password='',       # 密码
        database='test',   # 数据库名称
    )
    cursor = db.cursor()
    for i in range(1,11):
         username=input("请输入姓名")
         age=input("请输入年龄")
         sex=input("请输入性别")
         high=input("请输入身高")
         affected_rows=cursor.execute("insert into person(username,age,sex,high) values(%s,%s,%s,%s)",(username,age,sex,high))
         db.commit()
         if affected_rows == 1:
             print('新增成功！')
        # cursor.execute("select * from person")
    cursor.close()
    db.close()

#编程实现：将百度员工6万条数据，xlrd读取出来并使用pymysql写入到数据库中。
def OneMethods(db,cursor,sheet):
    for i in range(2, sheet.nrows):
        print(sheet.row_values(i))
        sql = "insert into staff(id,workId,name,username,password,phone,email,age,sex,addr,startdate,salary,responsibilities,company) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        affected_rows = cursor.execute(sql, sheet.row_values(i))
        db.commit()
        if affected_rows == 1:
            print('新增成功！')
        # if i==2:
        #     break

def TwoMethods(db,cursor,sheet):
    arr = []
    for i in range(2, sheet.nrows):
        # print(sheet.row_values(i))
        arr.append(sheet.row_values(i))
        # if i==3:
        #     break
    sql = "insert into staff(id,workId,name,username,password,phone,email,age,sex,addr,startdate,salary,responsibilities,company) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    affected_rows =cursor.executemany(sql, arr)
    db.commit()
    if affected_rows >= 1:
        print('影响条数%s,新增成功！'%affected_rows)

def function2():
    xls = xlrd.open_workbook("C:\\Users\\15420\\Desktop\\python自动化\第八天\\5.baidu-员工的人员信息.xls")
    sheet_names = xls.sheet_names()  # 获取所有 sheet 表格的名称
    print(sheet_names[0])
    sheet = xls.sheets()[0]  # 获取表格 sheets 对象
    # 连接 MySQL 数据库
    db = pymysql.connect(
        host='localhost',  # 主机名
        port=3306,  # 端口号
        user='root',  # 用户名
        password='',  # 密码
        database='test',  # 数据库名称
    )
    cursor = db.cursor()
    # OneMethods(db, cursor, sheet)
    TwoMethods(db, cursor, sheet)
    cursor.close()
    db.close()
if __name__ == '__main__':
    function1()
    # function2()
